package com.wmm.dao;

import com.wmm.bean.Student;
import com.wmm.bean.User;
import com.wmm.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImp implements StudentDao {
    @Override
    public boolean addStudent(Student student) {
        //1.创建QueryRunner对象 自动模式,传入数据库连接池
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

            //2.编写SQL
            String sql = "INSERT student VALUES(?,?,?,?,?,?,?)";

            Object[] parpm = {student.getId(),student.getName(),
                            student.getSex(),student.getBirthday(),
                            student.getMailbox(),student.getMemo(),
                            student.getClassNo()};
            //3.执行update, 不需要传入连接对象
            int num = qr.update(sql,parpm);
            if (num>0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean deleteStudent(String studentno) {
        //1.创建QueryRunner对象 自动模式,传入数据库连接池
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

            //2.编写SQL
            String sql = "delete from student where id = ?";

            Object[] parpm = {studentno};
            //3.执行update, 不需要传入连接对象
            int num = qr.update(sql,parpm);
            if (num>0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean updateStudent(Student student) {
        //1.创建QueryRunner对象 自动模式,传入数据库连接池
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

            //2.编写SQL
            String sql = "update student set name = ?,sex = ?,birthday=?,mailbox=?,memo=?,classNo = ? where id = ?";

            Object[] parpm = {  student.getName(),student.getSex(),
                                student.getBirthday(),student.getMailbox(),
                                student.getMemo(),student.getId(),student.getClassNo()};
            //3.执行update, 不需要传入连接对象
            int num = qr.update(sql,parpm);
            if (num>0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    @Override
    public List<Student> queryStudent(String id, String name) {
        String sql = null;
        List<Student> query = null;
        //1.创建QueryRunner对象 自动模式,传入数据库连接池
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

            //2.编写SQL
            if (null != id && null == name){
                sql = "select * from student where id = ?";
                Object[] parpm = {id};
                query = qr.query(sql, new BeanListHandler<Student>(Student.class),parpm);
            }else if (null == id && null != name){
                sql = "select * from student where name = ?";
                Object[] parpm = {name};
                query = qr.query(sql, new BeanListHandler<Student>(Student.class),parpm);
            }
            if (null != query){
                return query;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Student> findByPage(int start, int rows) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);
            String sql = "select * from student limit ?,?";
            Object[] parpm = {start,rows};
            List<Student> query = qr.query(sql, new BeanListHandler<Student>(Student.class),parpm);
            if (null != query){
                return query;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int findTotalCount() {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);
            String sql = "select count(*) from student";
            Object obj = qr.query(sql, new ScalarHandler());
            return Integer.parseInt(String.valueOf(obj));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Student> findqueryByPage(int start, int rows, String id, String name) {
        String sql = null;
        List<Student> query = null;

        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

            if (null != id && null == name) {    //学号不为空但是姓名为空的条件下
                sql = "select * from student where id = ? limit ?,?";

                Object[] parpm = {id,start,rows};
                query = qr.query(sql, new BeanListHandler<Student>(Student.class),parpm);

            } else if (null == id && null != name) {     //学号为空但是姓名不为空的条件下
                sql = "select * from student where name = ? limit ?,?";

                Object[] parpm = {name,start,rows};
                query = qr.query(sql, new BeanListHandler<Student>(Student.class),parpm);
            } else if(null != id && null != name){                                            //学号和姓名都不为空，根据两个条件查询
                sql = "select * from student where id = ? and name = ? limit ?,? ";

                Object[] parpm = {id,name,start,rows};
                query = qr.query(sql, new BeanListHandler<Student>(Student.class),parpm);

            }else{
                sql = "select * from student limit ?,? ";

                Object[] parpm = {start,rows};
                query = qr.query(sql, new BeanListHandler<Student>(Student.class),parpm);
            }


            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int findqueryTotalCount(String id, String name) {
        String sql = null;
        Object obj = null;
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

            if (null != id && null == name) {    //学号不为空但是姓名为空的条件下
                sql = "select count(*) from student where id = ?";

                Object[] parpm = {id};
                obj = qr.query(sql, new ScalarHandler<>(), parpm);

            } else if (null == id && null != name) {     //学号为空但是姓名不为空的条件下
                sql = "select count(*) from student where name = ?";

                Object[] parpm = {name};
                obj = qr.query(sql, new ScalarHandler<>(), parpm);

            } else if (null != id && null != name) {                                            //学号和姓名都不为空，根据两个条件查询
                sql = "select count(*) from student where id = ? and name = ?";

                Object[] parpm = {id,name};
                obj = qr.query(sql, new ScalarHandler<>(), parpm);
            }else{
                sql = "select count(*) from student";
                obj = qr.query(sql, new ScalarHandler());
            }
            return Integer.parseInt(String.valueOf(obj));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
